/*
 *                  BioJava development code
 *
 * This code may be freely distributed and modified under the
 * terms of the GNU Lesser General Public Licence.  This should
 * be distributed with the code.  If you do not have a copy,
 * see:
 *
 *      http://www.gnu.org/copyleft/lesser.html
 *
 * Copyright for this code is held jointly by the individual
 * authors.  These should be listed in @author doc comments.
 *
 * For more information on the BioJava project and its aims,
 * or to join the biojava-l mailing list, visit the home page
 * at:
 *
 *      http://www.biojava.org/
 * 
 * Created on Jun 22, 2007
 * 
 */

package org.biojava.services.das.dao;

import java.awt.Graphics;
import java.awt.Image;
import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;

import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.net.MalformedURLException;
import java.net.URL;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.imageio.ImageIO;
import javax.sql.DataSource;
import javax.swing.ImageIcon;

import org.biojava.services.das.registry.UserManager;
import org.biojava.services.das.servlets.ImageUtil;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;



public class DasUserDAO extends SimpleJdbcDaoSupport implements Serializable{

	/**
	 * 
	 */
	private static final long serialVersionUID = 7597485268611259117L;
	public static final int UNKNOWN_USER = -1;
	private static final int USER  = 1;
	private static final int ADMIN = 2;

	
	/** check if a DAS source should be linked to an owner 
	 * @param conn 
	 * @param auto_id 
	 * @throws SQLException */
	
	public void addDasSource2Owner(Connection conn, int auto_id) throws SQLException{
		String sql = "select user.user_id from registry, user " +
		" where  user.email = registry.adminemail and registry.auto_id = ?;";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1,auto_id);
		
		ResultSet set = ps.executeQuery();
		while( set.next()){
			int user_id = set.getInt(1);
			RegistryUser user = getUserById(conn, user_id);
			addOwner(conn, user, auto_id);
		}
		set.close();
		ps.close();
	}
	
	public boolean isNewUser(String email){

		//String sql = "select user_id  open_id,email,role_id from user where open_id ="+openid;
		String sql = "select user_id  from user where email = '"+email+"'";
		int result;
		try {
			result = getJdbcTemplate().queryForInt(sql);
		} catch (DataAccessException e) {
			e.printStackTrace();
			return true;
			
		}

		

		boolean found = true; // default is a new user
		if(result!=0){
			found = false;
		}
		System.out.println("isNewUser " + email + " " + found);
		return found;

	}

	/** get the user by database user_id, or null if not found
	 * 
	 * @param conn
	 * @param user_id
	 * @return the user or null, if not found
	 * @throws SQLException
	 */
	public RegistryUser getUserById(Connection conn, int user_id) throws SQLException {

		String sql = "select user_id,  open_id,email,role_id from user where user_id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setInt(1,user_id);

		RegistryUser user = null;
		ResultSet set = ps.executeQuery();
		while (set.next()){

			user = new RegistryUser();


			String oid = set.getString(2);
			String email = set.getString(3);
			int role_id = set.getInt(4);

			if ( role_id == ADMIN)
				user.setRole(Role.admin);
			else 
				user.setRole(Role.user);

			user.setEmail(email);
			user.setOpenID(oid);
			user.setUserID(user_id);


			break;
		}
		set.close();		
		ps.close();

		return user;
	}

	/** get the user with the OpenID, or null if not found
	 * 
	 * @param conn
	 * @param openid
	 * @return the user or null, if not found
	 * @throws SQLException
	 */
	public RegistryUser getUser(String openid)  {
//		if(!openid.endsWith("/")){
//			openid=openid+"/";//not sure why i now have to add this here?
//		}
		
		System.out.println("running method in DasUser with email="+openid);
		//String sql = "select user_id,  open_id,email,role_id,alert_me from user where open_id = ?";
		String sql="select user_id,  open_id,user.email,alert_me, role_id from user  where user.email = ?";
		//select user_id,  open_id,user.email,alert_me, user_roles.role_name from user, user_roles  where user.email ='jw12@sanger.ac.uk' and user_roles.email=user.email;
		if(openid==null || openid.equals("")){
			return null;
		}
		
		
		try {
			return (RegistryUser) getSimpleJdbcTemplate().queryForObject(sql, new RowMapper<RegistryUser>() {
						public RegistryUser mapRow(ResultSet rs, int rowNum)
								throws SQLException {
							RegistryUser user = new RegistryUser();
							user.setUserID(rs.getInt("user_id"));
							user.setOpenID(rs.getString("open_id"));
							user.setEmail(rs.getString("email"));
							if(rs.getInt("role_id")==2){
								user.setRole(Role.admin);
							}else{
								user.setRole(Role.user);
							}
							user.setAlertMe(rs.getBoolean("alert_me"));
							return user;
						}
					}, openid);
		} catch (EmptyResultDataAccessException e) {
			System.out.println("user does not exist "+openid);
			return null;
			
		}

	}

	public int getUserIdForOpenID(Connection conn, String openid) throws SQLException{
		String sql = "select user_id from user where open_id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setString(1,openid);

		int id = UNKNOWN_USER;
		ResultSet set = ps.executeQuery();
		while (set.next()){
			id = set.getInt(1);
			break;
		}
		set.close();		
		ps.close();

		return id;
	}


	/** get the aut_id for the last inserted statement
	 * 
	 * @param stmt
	 * @return the id 
	 * @throws SQLException
	 */
	public static int getLastInsertId(Statement stmt) throws SQLException {
		ResultSet rs = null;
		if (stmt != null ) {
			rs = stmt.getGeneratedKeys();
		}

		int id = -1; 
		if ( rs != null && rs.next()) {
			id = rs.getInt(1);
		} 

		rs.close();
		if ( id != -1 )
			return id;

		throw new SQLException ("No LastInsertId available");

	}

	/** write a user into the database, return the user_id for the user
	 * @param conn the database connection
	 * @param user the RegistryUser object to be serialized
	 * @return the user_id for the user
	 * @throws SQLException 
	 */
	public int storeUser(RegistryUser user) {
		int auto_id=0;
		String sql = "insert into user ( open_id,email,role_id,alert_me)" +
		" values (?,?,?,?)";
		int userRole=0;
		if (user.getRole().equals(Role.admin))
			userRole=ADMIN;
		else
			userRole=USER;
		int result;
		try {
			result = getSimpleJdbcTemplate().update(sql, new Object[]{user.getOpenID(), user.getEmail(), userRole, user.isAlertMe()});
		} catch (DataAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}
		//if more than 0 rows affected get the auto gen id for the row created else something went wrong
		
		auto_id=getSimpleJdbcTemplate().queryForInt("SELECT LAST_INSERT_ID()");
		
		
		//		ps.setString(1,user.getOpenID());
//		ps.setString(2,user.getEmail());
//		if (user.getRole().equals(Role.admin))
//			ps.setInt(3,ADMIN);
//		else
//			ps.setInt(3,USER);
//
//		if (user.isAlertMe())
//			ps.setInt(4,1);			
//		else
//			ps.setInt(4,0);
//
//		ps.executeUpdate();

		//int id = getLastInsertId(ps);
		//ps.close();
		//user.setUserID(id);
		return auto_id;
	}

	/**
	 * update email when a normal update but not with mirroring
	 * @param conn
	 * @param user
	 * @param auto_id
	 * @param updateEmail
	 * @throws SQLException
	 */
	public void addOwner(Connection conn, RegistryUser user,int auto_id, boolean updateEmail) 
	throws SQLException {

		boolean known = userOwnsAlready(conn, user, auto_id);
		if ( known )
			return;

		String sql = "insert into registry2user (auto_id, user_id) values(?,?)";		
		PreparedStatement ps = conn.prepareStatement(sql);
				
		ps.setInt(1,auto_id);
		ps.setInt(2,user.getUserID());
		
		ps.executeUpdate();
		ps.close();

		// set the user email field in the registry table...
		if(updateEmail){
		String sql2 = "update registry set adminemail=? where auto_id = ?";
		PreparedStatement ps2 = conn.prepareStatement(sql2);
		
		ps2.setString(1,user.getEmail());
		ps2.setInt(2,auto_id);
		ps2.executeUpdate();
		ps2.close();
		}
		
	}
	
	public void addOwner(Connection conn, RegistryUser user,int auto_id) throws SQLException{
		this.addOwner(conn, user, auto_id, true);
	}
	
	public void removeOwner(Connection conn, RegistryUser user,int auto_id) 
	throws SQLException {

		
		String sql = "delete from registry2user where  auto_id = ? and user_id =?";		
		PreparedStatement ps = conn.prepareStatement(sql);
				
		ps.setInt(1,auto_id);
		ps.setInt(2,user.getUserID());
		
		ps.executeUpdate();
		ps.close();

	}

	private boolean userOwnsAlready(Connection conn,
			RegistryUser user, int auto_id ) throws SQLException{
		
		String sql = "select count(*) from registry2user where auto_id = ? and user_id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		
		ps.setInt(1,auto_id);
		ps.setInt(2,user.getUserID());
		
		boolean known = false;

		ResultSet set = ps.executeQuery();
		while (set.next()){
			int count = set.getInt(1);				
			if (count > 0 )
				known = true;
		}
		set.close();
		ps.close();
		return known;
	}

	public void linkDasSourcesToUser(Connection conn, RegistryUser user) throws SQLException {
		//System.out.println("linking DasSources to user " + user.getUserID());


		String sql ="select auto_id from registry where adminemail ="+user.getEmail();
		
		List<Map<String, Object>>auto_ids=getJdbcTemplate().queryForList(sql);
		Iterator it=auto_ids.iterator();
		while (it.hasNext()){
			Map result=(Map)it.next();
			Integer auto_id =(Integer) result.get("auto_id"); 
			
			addOwner(conn,user,auto_id);

		}
	}

	/** update the known user in the database
	 * 
	 * @param conn
	 * @param user
	 * @throws SQLException 
	 */
	public void updateUser(Connection conn, RegistryUser user) throws SQLException{
		String sql = "update user set open_id = ?, email =? , role_id = ?, alert_me = ? " +
		" where user_id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1,user.getOpenID());
		ps.setString(2,user.getEmail());
		if ( user.getRole().equals(Role.admin)){
			ps.setInt(3,ADMIN);
		} else
			ps.setInt(3,USER);

		if (user.isAlertMe())
			ps.setInt(4,1);			
		else
			ps.setInt(4,0);

		ps.setInt(5,user.getUserID());
		ps.executeUpdate();
		ps.close();
	}

	public int deleteUser(int user_id) {
		//TODO: check also the other fields where this openid is used!

		String sql = "delete from user where user_id = "+user_id;
		int result=getJdbcTemplate().update(sql);
		
		//remove owner from das sources
		String removeFromRegSql= "delete from registry2user where user_id ="+user_id;
		int linesAffected=getJdbcTemplate().update(removeFromRegSql);
		//System.out.println("lines affected="+linesAffected);
		return result;
	}

	public void deleteProject(Connection conn, ProjectBean p)
	throws SQLException {
		String sql = "delete from project where project_id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setInt(1,p.getProjectID());
		ps.executeUpdate();

		ps.close();
	}
	
	public void updateProject(Connection conn, ProjectBean p) throws SQLException{
		String sql = "update project set name = ?, iconurl = ?, homepage =? ,"+ 
		" description = ? where project_id = ?";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1,p.getName());
		ps.setString(2,"");
		ps.setString(3,p.getHomepage().toString());
		ps.setString(4,p.getDescription());
		ps.setInt(5,p.getProjectID());
		ps.executeUpdate();
		ps.close();

	}

	public int createProject(Connection conn, ProjectBean p, int user_id) throws SQLException{

		String sql = "insert into project (name  , iconurl, homepage  ,"+ 
		" description, user_id) " +
		" values (?,?,?,?,?)";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1,p.getName());
		ps.setString(2,"");
		ps.setString(3,p.getHomepage().toString());
		ps.setString(4,p.getDescription());
		ps.setInt(5,user_id);
		ps.execute();

		int id = getLastInsertId(ps);
		p.setProjectID(id);
		ps.close();

		if ( p.getIconURL() == null ){
			// create a iconURL for this project
			//TODO write the ProjectIcon servlet 
			String iconURL = "ProjectIcon?id="+id;
			String sql2 = "update project set iconurl = ? where project_id = ?";
			PreparedStatement ps2 = conn.prepareStatement(sql2);
			ps2.setString(1,iconURL);
			ps2.setInt(2,id);
			ps2.executeUpdate();
			ps2.close();
		}
		return id;
	}


	public void addDasSource2Project(Connection conn, ProjectBean project, int auto_id)
	throws SQLException {
		String sql = "update registry set project_id =? where auto_id =?";
		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setInt(1,project.getProjectID());
		ps.setInt(2,auto_id);
		ps.executeUpdate();
		ps.close();

	}

	public void removeDasSourceFromProject(Connection conn,  int auto_id)
	throws SQLException {
		String sql = "update registry set project_id = 0 where auto_id =? ";
		PreparedStatement ps = conn.prepareStatement(sql);		
		ps.setInt(1,auto_id);
		ps.executeUpdate();
		ps.close();

	}
	
	
	


	/** write image to the database
	 * 
	 * @param conn
	 * @param p
	 * @param image
	 * @param imageFormat format of image, e.g. jpg or gif
	 * @throws IOException 
	 * @throws SQLException 
	 */
	public void writeProjectImage(Connection conn, ProjectBean p, Image image, String imageFormat)
	throws IOException, SQLException{

		//serialize image to database


		String sql = "update project set logo = ? where project_id= ? ";

		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setInt(2,p.getProjectID());

		byte[] byteImage = ImageUtil.toByteArray(image, imageFormat);

		ByteArrayInputStream inStream = new ByteArrayInputStream(byteImage);
		
		ps.setBinaryStream(1,inStream,inStream.available());

		ps.executeUpdate();
		ps.close();



	}

	/** load the Blob from the database and convert to an ImageIcon
	 * 
	 * @param conn
	 * @param p
	 * @return an ImageIcon or null
	 * @throws SQLException
	 */
	public ImageIcon getImageForProject(Connection conn, ProjectBean p) throws SQLException{
		
		ImageIcon dPhoto = null;
		String sql = "select logo from project where project_id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setInt(1, p.getProjectID());
		ResultSet set = ps.executeQuery();
		try {
			while (set.next()) {
				InputStream st = set.getBinaryStream(1);				
				BufferedImage buf = ImageIO.read(st);				
				BufferedImage bu = new BufferedImage(buf.getWidth(),buf.getHeight(),BufferedImage.TYPE_INT_RGB);
				Graphics g = bu.getGraphics();
				g.drawImage(buf, 0, 0, null);

				dPhoto = new ImageIcon(bu);
			}
		} catch (Exception e){
			e.printStackTrace();
		}

		set.close();
		ps.close();



//		Set the mime type of the image


		return dPhoto;


	}

	/** get the list of all projects
	 * 
	 * @param conn
	 * @return an array of projects
	 * @throws SQLException
	 */
	public ProjectBean[] getAllProjects(Connection conn) throws SQLException{
		List projects = new ArrayList();
		BeanFactory ctx=(new ClassPathXmlApplicationContext("SpringDAO.xml"));
		UserManager manager=(UserManager)ctx.getBean("userManager");
		String sql = "select name,iconurl,homepage,description,user_id,project_id from project";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet set = ps.executeQuery();
		while (set.next()){
			ProjectBean project = getProjectFromResultSet(set,manager);
			projects.add(project);
		}
		set.close();
		ps.close();

		return (ProjectBean[])projects.toArray(new ProjectBean[projects.size()]);
	}

	protected ProjectBean getProjectFromResultSet(ResultSet set,UserManager manager) throws SQLException{
		ProjectBean project = new ProjectBean();

		/*if (set.isBeforeFirst())
			set.next();
		if ( set.isAfterLast())
			return null;*/

		//if (set.getFetchSize() ==0)
		//return null;

		project.setName(set.getString(1));

		try {
			URL homepage = new URL(set.getString(3));
			project.setHomepage(homepage);
		} catch (MalformedURLException e){
			System.err.println(e.getMessage());
		}
		project.setDescription(set.getString(4));

		int user_id = set.getInt(5);
		if ( user_id != 0) {
			RegistryUser user = manager.getUserById(user_id);
			project.setOwner(user);
		}

		int project_id = set.getInt(6);
		project.setProjectID(project_id);

		return project;
	}


	public int getDasSourcesForProject(Connection conn, ProjectBean project)
	throws SQLException {

		String cmd = "select  from registry where project_id =?";

		PreparedStatement ps = conn.prepareStatement(cmd);
		ps.setInt(1,project.getProjectID());

		int nr =0;
		ResultSet set = ps.executeQuery();
		while (set.next()){
			nr = set.getInt(1);
		}
		set.close();
		ps.close();
		return nr;

	}

	public int getNrDasSourcesForProject(Connection conn, ProjectBean project)
	throws SQLException {

		String cmd = "select count(*) from registry where project_id =?";

		PreparedStatement ps = conn.prepareStatement(cmd);
		ps.setInt(1,project.getProjectID());

		int nr =0;
		ResultSet set = ps.executeQuery();
		while (set.next()){
			nr = set.getInt(1);
		}
		set.close();
		ps.close();
		return nr;

	}

	/** get the project information for this DAS source
	 * or null, if no project provided.
	 * @param conn
	 * @param das_source_url
	 * @return a ProjectBean or null if no project found
	 * @throws SQLException
	 */
	public ProjectBean getProjectForURL(Connection conn, String das_source_url)
	throws SQLException {


		String cmd1 = "select project_id from registry where url = ?";
		PreparedStatement ps = conn.prepareStatement(cmd1);
		ps.setString(1,das_source_url);

		ResultSet set = ps.executeQuery();
		int project_id = -1;
		while (set.next()){
			project_id = set.getInt(1);
		}
		set.close();
		ps.close();

		ProjectBean p = getProjectForId(conn, project_id);
		return p;


	}


	public boolean isOwner(Connection conn, RegistryUser user, int auto_id)
	throws SQLException{

		boolean isOwner = false;

		String cmd = "select ru_id from registry2user where auto_id = ? and user_id = ?";
		PreparedStatement ps = conn.prepareStatement(cmd);
		ps.setInt(1,auto_id);
		System.out.println("auto_id="+auto_id+ " user_id="+user.getUserID());
		
		ps.setInt(2,user.getUserID());
		ResultSet set = ps.executeQuery();

		while (set.next()){
			isOwner = true;
		}
		set.close();
		ps.close();

		return isOwner;
	}

	public RegistryUser[] getOwners(Connection conn, int auto_id)
	throws SQLException{
		List  owners = new ArrayList();

		String cmd = "select user_id from registry2user where auto_id = ?";
		PreparedStatement ps = conn.prepareStatement(cmd);
		ps.setInt(1,auto_id);

		ResultSet set = ps.executeQuery();

		while (set.next()){
			int user_id = set.getInt(1);
			RegistryUser user = getUserById(conn, user_id);
			owners.add(user);
		}
		set.close();
		ps.close();


		return (RegistryUser[]) owners.toArray(new RegistryUser[owners.size()]);
	}

	
	public RegistryUser[] getAllUsers(Connection conn)
	throws SQLException {
		List  owners = new ArrayList();

		String cmd = "select user_id from user ";
		PreparedStatement ps = conn.prepareStatement(cmd);
		

		ResultSet set = ps.executeQuery();

		while (set.next()){
			int user_id = set.getInt(1);
			RegistryUser user = getUserById(conn, user_id);
			owners.add(user);
		}
		set.close();
		ps.close();

		return (RegistryUser[]) owners.toArray(new RegistryUser[owners.size()]);
		
		
	}

	public ProjectBean getProjectForId(Connection conn, int project_id)
	throws SQLException {
		BeanFactory ctx=(new ClassPathXmlApplicationContext("SpringDAO.xml"));
		UserManager manager=(UserManager)ctx.getBean("userManager");

		String cmd = "select name,iconurl,homepage,description,user_id,project_id from project where project_id = ?";
		PreparedStatement ps = conn.prepareStatement(cmd);
		ps.setInt(1,project_id);
		ResultSet set = ps.executeQuery();

		ProjectBean project = null ;
		while (set.next()){
			project = getProjectFromResultSet(set,manager);

		}
		set.close();
		ps.close();
		return project;
	}

	public ProjectBean[] getAdminProjectsForUser(Connection conn, RegistryUser user )
	throws SQLException{
		BeanFactory ctx=(new ClassPathXmlApplicationContext("SpringDAO.xml"));
		UserManager manager=(UserManager)ctx.getBean("userManager");

		String cmd = "select name,iconurl,homepage,description,user_id,project_id " +
		" from project where user_id = ?";

		PreparedStatement ps = conn.prepareStatement(cmd);
		ps.setInt(1,user.getUserID());
		ResultSet set = ps.executeQuery();

		List projects = new ArrayList();
		while (set.next()){
			projects.add(getProjectFromResultSet(set,manager));

		}
		set.close();
		ps.close();
		return (ProjectBean[]) projects.toArray(new ProjectBean[projects.size()]);

	}


	/**
	 * is the username and password correct
	 * @return
	 */
	public boolean authenticateUser(String username, String password){
		//check the database
		boolean ok=false;
		
		
		return ok;
	}

	public void removeDasSource(String serverurl) {
		
		
	}

}
